
import xlwt
import xlrd
import datetime
import json
import io
from decimal import Decimal


# 导出表格
def exportExcelForShop(_itemOrders):
    workbook = xlwt.Workbook(encoding = 'utf-8')
    worksheet = workbook.add_sheet('商户结算记录')

    style = xlwt.XFStyle()
    #设置样式
    alignment = xlwt.Alignment()
    # 水平位置：0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.horz = 0x01
    # 垂直方向：0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    alignment.vert = 0x01
    style.alignment = alignment
    #设置字体
    font = xlwt.Font()
    # font.bold = True
    style.font = font

    # worksheet.col(0).width = 4000
    # worksheet.col(1).width = 3000
    # worksheet.col(2).width = 4000
    # worksheet.col(3).width = 4000

    # row_first = ['订单号', '购买用户ID', '购买用户手机号', '订单状态', '提货方式', '收货人', '收货人手机号', '送货地址', '买家留言', '支付方式', '支付时间', '下单时间', '订单总金额', '商品名称', '商品规格', '单价', '商品数量']
    row_first = ['记录时间', '订单号', '商户', '订单金额','提现金额', '手续费', '实际到账', '处理状态', '真实姓名', '银行名称', '收款卡号', '备注']
    # 设置第一行
    for col_index, title in enumerate(row_first):
        style1 = xlwt.XFStyle()
        font1 = xlwt.Font()
        font1.bold = True
        style1.font = font1
        worksheet.write(0, col_index, title, style1)
    row_index = 1
    for row_i, record in enumerate(_itemOrders):
        last_col_index = 0
        for col_index, title in enumerate(row_first):
            if title == '记录时间':
                createTime = str(record.createTime)
                if createTime and len(createTime)>=19:
                    createTime = createTime[0:10] + ' ' + createTime[11:19]
                else:
                    createTime = ''
                worksheet.write(row_index, col_index, createTime, style)
                worksheet.col(col_index).width = 5000
            if title == '订单号':
                worksheet.write(row_index, col_index, record.orderId, style)
                worksheet.col(col_index).width = 6000
            if title == '商户名称':
                worksheet.write(row_index, col_index, record.shop.name if record.shop else '', style)
                worksheet.col(col_index).width = 2000
            if title == '订单金额':
                worksheet.write(row_index, col_index, record.moneyLocked, style)
                worksheet.col(col_index).width = 3000
            if title == '结算金额':
                worksheet.write(row_index, col_index, record.money, style)
                worksheet.col(col_index).width = 3000
            if title == '手续费':
                worksheet.write(row_index, col_index, record.serviceCharge, style)
                worksheet.col(col_index).width = 3000
            if title == '实际到账':
                worksheet.write(row_index, col_index, record.realMoney, style)
                worksheet.col(col_index).width = 3000
            if title == '处理状态':
                orderStatus_display = record.get_orderStatus_display()
                if orderStatus_display == '待发送':
                    orderStatus_display = '待发放'
                elif orderStatus_display == '完成':
                    orderStatus_display = '已发放'
                worksheet.write(row_index, col_index, orderStatus_display or '未知', style)
                worksheet.col(col_index).width = 2000
            receiptAccount = {}
            if record.receiptAccount:
                receiptAccount = json.loads(record.receiptAccount)
            else:
                receiptAccount = {
                    'creditedName': record.payWayAccount.realname if record.payWayAccount else '',
                    'creditedBank': record.payWayAccount.accountName if record.payWayAccount else '',
                    'creditedAccount': record.payWayAccount.account if record.payWayAccount else ''
                }
            if title == '真实姓名':
                worksheet.write(row_index, col_index, receiptAccount.get('creditedName'), style)
                worksheet.col(col_index).width = 3000
            if title == '银行名称':
                worksheet.write(row_index, col_index, receiptAccount.get('creditedBank'), style)
                worksheet.col(col_index).width = 8000
            if title == '收款卡号':
                worksheet.write(row_index, col_index, receiptAccount.get('creditedAccount'), style)
                worksheet.col(col_index).width = 8000
            if title == '备注':
                worksheet.write(row_index, col_index, record.remark, style)
                worksheet.col(col_index).width = 6000
        row_index = row_index+1

    output = io.BytesIO()
    workbook.save(output)
    output.seek(0)
    return output